---
sidebar_position: 7
---
# ClickHouse

The ClickHouse backend supports both **ingestion** and **querying** via a ClickHouse [HTTP](https://clickhouse.com/docs/interfaces/http) endpoint.

## Behavior and Configuration

### Configuration

The following values are required when creating a ClickHouse backend:

- `url`: (`string`, required) a valid HTTP(S) endpoint.
- `username`: (`string`, required) used for auth.
- `password`: (`string`, required) used for auth.
- `database`: (`string`, required) the database to use.
- `port`: (`number`, required) the port to use.


### User Grants

The ClickHouse backend requires the following [user grants](https://clickhouse.com/docs/sql-reference/statements/grant):

- `INSERT`
- `SELECT`
- `CREATE TABLE`
- `ALTER TABLE`
- `DROP TABLE`,
- `CREATE VIEW`
- `DROP VIEW`

:::note
It is suggested that you create a dedicated database and user specifically for Logflare to isolate data and permissions.
:::

## Implementation Details

The ClickHouse backend will automatically attempt to provision required tables and views after receiving the first log event.

The ingest table schema is as follows:

- `id`: The log event `UUID`.
- `body`: The processed log event, stored as serialized JSON in a `String` column.
- `timestamp`: Unix microsecond, stored as `DateTime64(6)`

### Ingestion Settings

Logflare writes logs to ClickHouse asynchronously by setting `async_insert = 1` and `wait_for_async_insert = 1` on each insert operation.
Gzip compression is also applied to the data before sending to ClickHouse.

### Table Engine

By default, the ClickHouse backends will utilize the [`MergeTree` engine](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree).

Note that when using ClickHouse Cloud, replication is handled automatically as mentioned in the [data replication documentaion](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication#creating-replicated-tables).

## Querying

ClickHouse backends support SQL querying through Logflare Endpoints and Alerts. The backend uses ClickHouse SQL dialect, which supports standard SQL features including:

- Common Table Expressions (CTEs) with `WITH` clauses
- Complex aggregations and window functions
- Array and nested data type operations
- ClickHouse-specific functions (e.g., `tuple()`, `arraySlice()`, `JSONExtractString()`)

### Sandboxed Queries

ClickHouse backends fully support [sandboxed queries](/concepts/endpoints#query-sandboxing) within Endpoints, allowing you to create secure, parameterized API endpoints where consumers can provide custom SQL while being restricted to pre-defined data subsets.

Example sandboxed ClickHouse endpoint:

```sql
WITH filtered_logs AS (
    SELECT id, event_message, timestamp
    FROM my_clickhouse_source
    WHERE timestamp > now() - interval 1 day
)
SELECT * FROM filtered_logs
```

Consumers can then query within the sandbox via the `sql=` parameter:

```sql
SELECT event_message, count(*) as count
FROM filtered_logs
GROUP BY event_message
ORDER BY count DESC
```

See the [Endpoints documentation](/concepts/endpoints) for more details on sandboxed queries and security features.
